﻿using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NET6.WebAPI.Dtos.Tool;
using NET6.WebAPI.Entities.Base;
using NET6.WebAPI.Exceptions;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Data.Common;
using System.Linq.Expressions;
using static NET6.WebAPI.Entities.Base.BusinessBase;

namespace NET6.WebAPI.Extensions
{
    public static class EntityFrameworkExtension
    {
        /// <summary>
        /// 异步获取一条记录, 并检查记录是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="whereExpression">查询条件委托</param>
        /// <returns></returns>
        public static async Task<T> GetFirstAsync<T>
            (this IQueryable<T> queryable, Expression<Func<T, bool>> whereExpression)
        {
            var data = await queryable.FirstAsync(whereExpression);
            return data != null ? data : throw new NotExistsException();
        }

        /// <summary>
        /// 异步获取分页数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="pageNumber">页数</param>
        /// <param name="pageSize">页行数</param>
        /// <returns></returns>
        public static async Task<PagingDto<T>> GetPageListAsync<T>
            (this IQueryable<T> queryable, int pageNumber, int pageSize) where T : class
        {
            int totalCount = await queryable.CountAsync();
            var datas = await queryable.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToListAsync();

            return new PagingDto<T>(totalCount, pageSize, datas);
        }

        /// <summary>
        /// 异步检查记录是否存在
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="queryable"></param>
        /// <param name="whereExpression">查询条件委托</param>
        /// <returns></returns>
        public static async Task CheckExistsAsync<T>
            (this IQueryable<T> queryable, Expression<Func<T, bool>> whereExpression)
        {
            if (await queryable.Where(whereExpression).AnyAsync())
            {
                throw new NameDuplicateException();
            }
        }

        /// <summary>
        /// 异步添加和保存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public static async Task<bool> AddAndSaveAsync<T>(this DbContext db, T? entity) where T : class
        {
            if (entity != null)
            {
                await db.Set<T>().AddAsync(entity);
                return await db.SaveAsync();
            }

            return false;
        }

        /// <summary>
        /// 异步逻辑删除和保存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static async Task<bool> LogicalDeleteAndSaveAsync<T>(this DbContext db, T? entity) 
            where T : BusinessBase
        {
            if (entity != null)
            {
                entity.RecordStatus = RecordStatusEnum.Deleted;
                entity.DeleteTime = DateTime.Now;
                return await db.SaveAsync();
            }

            return false;
        }

        /// <summary>
        /// 异步保存
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public static async Task<bool> SaveAsync(this DbContext db)
        {
            return await db.SaveChangesAsync() > 0;
        }

        #region 原生 SQL

        /// <summary>
        /// 执行原生 SQL 查询(支持多个语句)
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql">查询语句</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static DataSet ExecuteSqlQuery(this DbContext db, string? sql, params DbParameter[] parms)
        {
            var ds = new DataSet();

            if (sql == null) return ds;

            using var cmd = db.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            if (cmd.Connection != null && cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            cmd.Parameters.AddRange(parms);

            var adapter = new SqlDataAdapter(cmd as SqlCommand);
            adapter.Fill(ds);

            return ds;
        }

        /// <summary>
        /// 获取 SQL 分页数据
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql">查询语句</param>
        /// <param name="orderField">排序字段字符串</param>
        /// <param name="pageNumber">页数</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="parms">参数数组</param>
        /// <returns></returns>
        public static PagingDto<DataTable> GetSqlPageList(this DbContext db, 
            string? sql, string? orderField, int pageNumber, int pageSize, params DbParameter[] parms)
        {
            if (sql == null) 
            {
                return new PagingDto<DataTable> { };
            }

            string countSql = $"select count(*) as totalCount from ({sql}) query";
            string pageSql = $@"
                select * from ({sql}) query 
                order by {orderField} 
                offset ({pageNumber} - 1) * {pageSize} rows fetch next {pageSize} rows only";

            //string pageSql = $@"
            //    select * from (
            //        select row_number() over(order by {orderField}) as rn, * 
            //        from ({sql}) as ol
            //    ) as tmp
            //    where rn between (({pageNumber} - 1) * {pageSize} + 1) and ({pageNumber} * {pageSize})";

            string fullSql = countSql + ";" + pageSql;

            var ds = db.ExecuteSqlQuery(fullSql, parms);
            if (ds.Tables.Count == 2)
            {
                int totalCount = ds.Tables[0].Rows[0][0].ToInt();
                var datas = ds.Tables[1];
                //return new PagingDto<DataTable>(totalCount, pageSize, datas.ToList());
                return new PagingDto<DataTable>(totalCount, pageSize, JArray.FromObject(datas));
            }

            return new PagingDto<DataTable>();
        }

        #endregion
    }
}
